﻿using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;

namespace Libraries.Repositories
{
    public class RepositorySql
    {
        public static class SqlHelper
        {
            /// <summary>
            /// Execute a stored-procedure and return IDataReader
            /// </summary>
            /// <param name="storedProcedureName"></param>
            /// <param name="parameterList"></param>
            /// <param name="connectionString"> </param>
            /// <returns></returns>
            public static IDataReader ExecuteReader(string storedProcedureName, Collection<SqlParameter> parameterList, string connectionString)
            {
                IDataReader result = null;
                try
                {
                    var conn = new SqlConnection(connectionString);
                    conn.Open();

                    var cmd = new SqlCommand(storedProcedureName, conn) { CommandType = CommandType.StoredProcedure };
                    cmd.Parameters.Clear();

                    if (parameterList != null)
                    {
                        foreach (var t in parameterList)
                        {
                            cmd.Parameters.Add(t);
                        }
                    }

                    result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (parameterList != null)
                    {
                        foreach (var t in parameterList)
                        {
                            if (t.Direction == ParameterDirection.Output ||
                                t.Direction == ParameterDirection.InputOutput ||
                                t.Direction == ParameterDirection.ReturnValue) // if is output
                            {
                                t.Value = cmd.Parameters[t.ParameterName].Value;
                            }
                        }
                    }
                    return result;
                }
                catch
                {
                    if (result != null)
                    {
                        result.Close();
                    }
                    throw;
                }
            }

            /// <summary>
            /// 
            /// </summary>
            /// <param name="storedProcedureName"></param>
            /// <param name="connectionString"> </param>
            /// <returns></returns>
            public static IDataReader ExecuteReader(string storedProcedureName, string connectionString)
            {
                IDataReader result = null;
                try
                {
                    var conn = new SqlConnection(connectionString);
                    conn.Open();

                    var cmd = new SqlCommand(storedProcedureName, conn) { CommandType = CommandType.StoredProcedure };
                    cmd.Parameters.Clear();

                    result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    return result;
                }
                catch
                {
                    if (result != null)
                    {
                        result.Close();
                    }
                    throw;
                }
            }

            /// <summary>
            /// 
            /// </summary>
            /// <param name="storedProcedureName"></param>
            /// <param name="parameterList"></param>
            /// <param name="connectionString"> </param>
            /// <returns></returns>
            public static int ExecuteNonQuery(string storedProcedureName, Collection<SqlParameter> parameterList, string connectionString)
            {
                var conn = new SqlConnection(connectionString);
                conn.Open();

                var cmd = new SqlCommand(storedProcedureName, conn) { CommandType = CommandType.StoredProcedure };
                cmd.Parameters.Clear();

                if (parameterList != null)
                {
                    foreach (var t in parameterList)
                    {
                        cmd.Parameters.Add(t);
                    }
                }
                var result = cmd.ExecuteNonQuery();

                if (parameterList != null)
                {
                    foreach (var t in parameterList)
                    {
                        if (t.Direction == ParameterDirection.Output ||
                            t.Direction == ParameterDirection.InputOutput ||
                            t.Direction == ParameterDirection.ReturnValue)
                            t.Value = cmd.Parameters[t.ParameterName].Value;
                    }
                }
                conn.Dispose();
                return result;
            }

            ///<summary>
            /// ExecuteNonQuery
            ///</summary>
            ///<param name="conn">Connection</param>
            ///<param name="transaction"></param>
            ///<param name="storedProcedureName">Store procedure</param>
            ///<param name="parameterList">List of parameters</param>
            ///<returns></returns>
            public static int ExecuteNonQuery(SqlConnection conn, SqlTransaction transaction, string storedProcedureName,
                                              Collection<SqlParameter> parameterList)
            {
                var cmd = new SqlCommand(storedProcedureName, conn, transaction) { CommandType = CommandType.StoredProcedure };
                cmd.Parameters.Clear();

                if (parameterList != null)
                {
                    foreach (var t in parameterList)
                    {
                        cmd.Parameters.Add(t);
                    }
                }
                var result = cmd.ExecuteNonQuery();

                if (parameterList != null)
                {
                    foreach (var t in parameterList)
                    {
                        if (t.Direction == ParameterDirection.Output ||
                            t.Direction == ParameterDirection.InputOutput ||
                            t.Direction == ParameterDirection.ReturnValue)
                            t.Value = cmd.Parameters[t.ParameterName].Value;
                    }
                }
                return result;
            }

            /// <summary>
            /// ExecuteNonQuery
            /// </summary>
            /// <param name="storedProcedureName"></param>
            /// <param name="connectionString"> </param>
            /// <returns></returns>
            public static int ExecuteNonQuery(string storedProcedureName, string connectionString)
            {
                var conn = new SqlConnection(connectionString);
                conn.Open();

                var cmd = new SqlCommand(storedProcedureName, conn) { CommandType = CommandType.StoredProcedure };
                cmd.Parameters.Clear();
                var result = cmd.ExecuteNonQuery();
                conn.Dispose();
                return result;
            }

            ///<summary>
            ///</summary>
            ///<param name="conn">Connection</param>
            ///<param name="transaction"></param>
            ///<param name="storedProcedureName">Store procedure</param>
            ///<returns></returns>
            public static int ExecuteNonQuery(SqlConnection conn, SqlTransaction transaction, string storedProcedureName)
            {
                var cmd = new SqlCommand(storedProcedureName, conn, transaction) { CommandType = CommandType.StoredProcedure };
                cmd.Parameters.Clear();
                var result = cmd.ExecuteNonQuery();
                return result;
            }
        }
    }
}